﻿/*
 * 
 *  文件名 ：Student.cs
 *  类  名 ：XHCTMS.DAL.Student
 * 
 *  创建人 ：张夏
 *  创建时间 ：2013-05-21
 *  备注 ：对数据库学生表的增删改查。
 *  
 */



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace XHCTMS.DAL
{
    /// <summary>
    /// 学生实体的操作
    /// </summary>
    public class Student
    {
        /// <summary>
        /// 获取部分在校学生信息
        /// </summary>
        /// <param name="part">页数（1开始，每页30条记录）</param>
        /// <returns>返回类型 DataTable  异常/null</returns>
        public DataTable GetPart(int part)
        {
            StringBuilder sb = new StringBuilder();

//            sb.Append(@"select * from ( select top 50  *  from (select top (@part*50) tstu.student_id,tstu.xhstudent_Id,
//tstu.student_name,tsub.sub_name,tsub.sub_years,tc.class_name,tu.[user_name] from student tstu 
//left join class tc on tstu.student_class_Id = tc.class_id
// left join sub tsub on tc.class_sub_id =  tsub.sub_id left join [user] tu on tc.class_teacher_Id = tu.[user_id] where student_state =1
// order by tsub.sub_name,tc.class_name,tstu.student_name ) A order by A.sub_name desc,A.class_name desc,A.student_name desc ) B 
// order by B.sub_name, B.class_name,B.student_name");
            //student_state =1 为在校学生

            //            sb.Append(@"select  top 30 A.student_id,A.xhstudent_Id,A.student_name,A.sub_name,A.class_name,A.[user_name]
            // from  ( select ROW_NUMBER() over(order by student_id)as row,t1.*,t2.*,t3.sub_name,t4.[user_name] from student t1 
            //left join class t2 on t1.student_class_Id = t2.class_id left join sub t3 on t3.sub_id = t2.class_sub_id 
            // left join [user] t4 on t4.[user_id] = t2.[class_teacher_Id]
            //where student_state =1  ) A  where row > 30*(@part-1)");
            sb.Append(@" select top 50 tstu.student_id,tstu.xhstudent_Id,
                tstu.student_name,tsub.sub_name,tsub.sub_years,tc.class_id,tc.class_name,tu.[user_name] from  student tstu 
                left join class tc on tstu.student_class_Id = tc.class_id
                 left join sub tsub on tc.class_sub_id =  tsub.sub_id left join [user] tu on tc.class_teacher_Id = tu.[user_id] where  student_state =1
                 and tstu.student_id not in
                (select top (50*@part) tstu.student_id from student tstu 
                left join class tc on tstu.student_class_Id = tc.class_id
                 left join sub tsub on tc.class_sub_id =  tsub.sub_id left join [user] tu on tc.class_teacher_Id = tu.[user_id] where student_state =1
                 order by tsub.sub_name ,tc.class_name ,tstu.student_name 
                 )order by tsub.sub_name ,tc.class_name ,tstu.student_name ");
            SqlParameter[] paras = { new SqlParameter("@part", SqlDbType.Int) };
            paras[0].Value = part -1;
            DataTable dt = new DBHELP.Common().GetDataTable(sb.ToString(), paras);
            return dt;
        }




        /// <summary>
        /// 增加学生
        /// </summary>
        /// <param name="stu">学生实例</param>
        /// <returns>返回结果：int  增加成功/1； 失败/0； 异常/-1；</returns>
        public int Add(MODEL.Student stu)
        {
            {
                int verityResult = verify(stu);
                if (verityResult != -10)
                {
                    return verityResult;
                }
            }
            StringBuilder sb = new StringBuilder();
            sb.Append(@"insert into Student values(@XHStudentID,@StudentName,@StudentGender,
@StudentPersonID,@StudentClassID,default)");
            SqlParameter[] paras ={ new SqlParameter("@XHStudentID",SqlDbType.NVarChar,32),
                                  new SqlParameter("@StudentName",SqlDbType.NVarChar,8),
                                  new SqlParameter("@StudentGender",SqlDbType.NVarChar,4),
                                  new SqlParameter("@StudentPersonID",SqlDbType.NVarChar,64),
                                  new SqlParameter("@StudentClassID",SqlDbType.Int)
                                  };
            paras[0].Value = stu.XHStudentID;
            paras[1].Value = stu.StudentName;
            paras[2].Value = stu.StudentGender;
            if (string.IsNullOrEmpty(stu.StudentPersonID))
            {
                paras[3].Value = DBNull.Value;
            }
            else
            {
                paras[3].Value = stu.StudentPersonID;
            }
            paras[4].Value = stu.StudentClassID;
            int result = new DBHELP.Common().ExcuteQuery(sb.ToString(), paras);
            return result;
        }

        /// <summary>
        /// 增加学生
        /// </summary>
        /// <param name="xHStuID">新华学号</param>
        /// <param name="stuName">姓名</param>
        /// <param name="stuGender">性别</param>
        /// <param name="stuPersonID">身份证号</param>
        /// <param name="stuClassID">班级名</param>
        /// <param name="stuState">在校状况（0：毕业； 1：在校； 2：休学）</param>
        /// <returns>返回结果：int  增加成功/1； 失败/0； 异常/-1；无班级/-2;</returns>
        public int Add(string xHStuID, string stuName, string stuGender,
            string stuPersonID, int stuClassID)
        {          

            MODEL.Student stu = ToStudentModel(0,xHStuID, stuName, stuGender, stuPersonID, stuClassID,"");
            if (stu == null)
            {
                return -1;//异常 
            }
            if (stu.StudentClassID == -1)
            {
                return -2; //无班级
            }
            int result = Add(stu);
            return result;
        }


        #region 注释：删除学生
        ///// <summary>
        ///// 删除学生（根据学生好号或者新华学生号)
        ///// </summary>
        ///// <param name="stuID">Int类型学生号或者String类型新华学号</param>
        ///// <returns>返回结果：int  成功/1；失败/0 ；异常/-1</returns>
        //public int Delete(object stuID)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    SqlParameter[] paras = new SqlParameter[]{};
        //    if (stuID is int)
        //    {
        //        sb.Append("delete student where student_id =@StuID");
        //        paras =new SqlParameter[]{ new SqlParameter("@StuID", SqlDbType.Int) };
        //    }
        //    else
        //    {
        //        if (stuID is string)
        //        {
        //            sb.Append("delete student where xhstudent_Id =@XHStuID");
        //            paras = new SqlParameter[]{ new SqlParameter("@XHStuID", SqlDbType.NVarChar, 32) };
        //        }
        //        else
        //        {
        //            return -1;
        //        }
        //    }        
        //    int result = new DBHELP.Common().ExcuteQuery(sb.ToString(), paras);
        //    return result;
        //}
        #endregion


      
       /// <summary>
        /// 修改学生信息
       /// </summary>
       /// <param name="stuID"></param>
       /// <param name="xHStuID"></param>
       /// <param name="stuName"></param>
       /// <param name="stuClassId"></param>
       /// <param name="stuFlag"></param>
        /// <returns>返回结果：int 成功/1； 失败/0； 异常/-1</returns>
        public int Alter(int stuID,string xHStuID ,string stuName ,int stuClassId,int stuState =1)
        {
            
            StringBuilder sb = new StringBuilder();
            sb.Append(@"update student set xhstudent_Id=@XHStudentID,student_name=@StudentName,
          student_class_Id =@StudentClassID,student_state=@StudentState where student_id =@StudentID");         
            SqlParameter[] paras ={ new SqlParameter("@StudentID",SqlDbType.Int),
                                  new SqlParameter("@XHStudentID",SqlDbType.NVarChar,32),
                                  new SqlParameter("@StudentName",SqlDbType.NVarChar,8),                                                         
                                  new SqlParameter("@StudentClassID",SqlDbType.Int),
                                  new SqlParameter("@StudentState",SqlDbType.Int)};
            paras[0].Value = stuID;



            paras[1].Value = xHStuID;


            paras[2].Value = stuName;

            paras[3].Value = stuClassId;


            paras[4].Value = stuState; //在校状况（0：毕业； 1：在校； 2：休学； 3开除）
            int result = new DBHELP.Common().ExcuteQuery(sb.ToString(), paras);
            return result;
        }

       

        /// <summary>
        /// 查找学生
        /// </summary>
        /// <param name="parasValue">查找参数(新华学号，学生名，专业名，专业学制, 班级名，班主任名)</param>
        /// <returns></returns>
        public DataTable Search(string[] parasValue ,int page =1)
        {
            DataTable dt = new DataTable();
            string[] parasStr = new string[] { " tstu.xhstudent_Id =", " tstu.student_name =", " tsub.sub_name ="," tsub.sub_years=",
            "tc.class_name =","tu.[user_name]="};
            string[] parasName = new string[]{"@XHStudentID","@StudentName","@SubName","@SubYears","@ClassName",
            "@UserName"};
            if (parasValue.Length> parasStr.Length)
            {
                dt.TableName = "ArraryLengthError";
                return dt;
            }
            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            List<SqlParameter> paras = new List<SqlParameter> { };
//            sb.Append(@"select * from ( select top 50  *  from (select top (@page*50) tstu.student_id,tstu.xhstudent_Id,
//tstu.student_name,tsub.sub_name,tsub.sub_years,tc.class_name,tu.[user_name] from student tstu 
//left join class tc on tstu.student_class_Id = tc.class_id
// left join sub tsub on tc.class_sub_id =  tsub.sub_id left join [user] tu on tc.class_teacher_Id = tu.[user_id] where student_state =1 ");

            sb.Append(@" select top 50 tstu.student_id,tstu.xhstudent_Id,
                tstu.student_name,tsub.sub_name,tsub.sub_years,tc.class_id,tc.class_name,tu.[user_name] from  student tstu 
                left join class tc on tstu.student_class_Id = tc.class_id
                 left join sub tsub on tc.class_sub_id =  tsub.sub_id left join [user] tu on tc.class_teacher_Id = tu.[user_id] where 
                 tstu.student_state =1 ");
            sb2.Append(@" and tstu.student_id not in
                (select top (50*@page) tstu.student_id from student tstu 
                left join class tc on tstu.student_class_Id = tc.class_id
                 left join sub tsub on tc.class_sub_id =  tsub.sub_id left join [user] tu on tc.class_teacher_Id = tu.[user_id] where student_state =1 ");
                  



           // bool isFirst = true;
            for (int count = 0; count < parasValue.Length; count++)
            {
                if (!string.IsNullOrEmpty(parasValue[count]))
                {
                    //  switch (isFirst)
                    // {
                    //   case true:
                    //  sb.Append("where " + parasStr[count] + parasName[count]);
                    //  paras.Add(new SqlParameter(parasName[count], parasValue[count]));
                    //  isFirst = false;
                    //   break;
                    //   default:
                    sb.Append(" and " + parasStr[count] + parasName[count]);
                    sb2.Append(" and " + parasStr[count] + parasName[count]);
                    paras.Add(new SqlParameter(parasName[count], parasValue[count]));
                    //    break;
                }

                  
            }

//            sb.Append(@" order by tsub.sub_name,tc.class_name,tstu.student_name ) A order by A.sub_name desc,A.class_name desc,A.student_name desc ) B 
// order by B.sub_name, B.class_name,B.student_name ");
            sb.Append(sb2.ToString()+" order by tsub.sub_name ,tc.class_name ,tstu.student_name )");
           
     
            sb.Append(@" order by tsub.sub_name ,tc.class_name ,tstu.student_name ");
            paras.Add(new SqlParameter("@page", page-1));
            dt = new DBHELP.Common().GetDataTable(sb.ToString(), paras);
            return dt;
        }

        /// <summary>
        /// 获得所有满足条件的学生数量
        /// </summary>
        /// <param name="parasValue">学生属性参数(新华学号，学生名，专业名，专业学制, 班级名，班主任名)</param>
        /// <returns></returns>
        public int MaxPage(string[] parasValue)
        {
            DataTable dt = new DataTable();
            string[] parasStr = new string[] { " t1.xhstudent_Id =", " t1.student_name =", " t3.sub_name ="," t3.sub_years=",
            "t2.class_name =","t4.[user_name]="};
            string[] parasName = new string[]{"@XHStudentID","@StudentName","@SubName","@SubYears","@ClassName",
            "@UserName"};
            if (parasValue.Length > parasStr.Length)
            {
                return -101;
            }
            StringBuilder sb = new StringBuilder();
            List<SqlParameter> paras = new List<SqlParameter> { };
            sb.Append(@"select COUNT(student_id) from student t1 left join class t2 on t1.student_class_Id = t2.class_id
 left join sub t3 on t2.class_sub_id =  t3.sub_id left join [user] t4 on t2.class_teacher_Id = t4.[user_id] where student_state =1 ");
           
          //  bool isFirst = true;
            for (int count = 0; count < parasValue.Length; count++)
            {
                if (!string.IsNullOrEmpty(parasValue[count]))
                {
                    //  switch (isFirst)
                    //  {
                    //      case true:
                    //      sb.Append("where " + parasStr[count] + parasName[count]);
                    //      paras.Add(new SqlParameter(parasName[count], parasValue[count]));
                    //      isFirst = false;
                    //      break;
                    //      default:
                    sb.Append(" and " + parasStr[count] + parasName[count]);
                    paras.Add(new SqlParameter(parasName[count], parasValue[count]));
                    //      break;
                }
            }
                 
          
            dt = new DBHELP.Common().GetDataTable(sb.ToString(), paras);
            return int.Parse(dt.Rows[0][0].ToString()) ;
        }


        /// <summary>
        /// 学生毕业
        /// </summary>
        /// <param name="stuID"></param>
        /// <returns></returns>
        public int StuGraduation(int stuID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"update student set  student_state=0 where student_id =@StudentID");
            SqlParameter[] paras ={ new SqlParameter("@StudentID",SqlDbType.Int)};
            paras[0].Value = stuID;
            return new DBHELP.Common().ExcuteQuery(sb.ToString(), paras);
        }




        #region 类私有方法     

        #region 注释：查看新华学好使用次数，用sql unique替代
        ///// <summary>
        ///// 查看新华学好使用次数
        ///// </summary>
        ///// <param name="stuXHID">新华学号</param>
        ///// <returns>返回结果：int</returns>
        //private int XHStuIDEnable(string stuXHID)
        //{
        //    if (string.IsNullOrEmpty(stuXHID))
        //    {
        //        return -1;
        //    }

        //    StringBuilder sb = new StringBuilder("select xhstudent_Id from student where xhstudent_Id = @XHStudentID");
        //    SqlParameter[] paras = { new SqlParameter("@XHStudentID", SqlDbType.NVarChar, 32) };
        //    paras[0].Value = stuXHID;
        //    DataTable dt = new DBHELP.Common().GetDataTable(sb.ToString(), paras);
        //    return dt.Rows.Count;
        //}
        #endregion

        /// <summary>
        /// 将学生信息组成学生类
        /// </summary>
        /// <param name="stuID">学生号(无可填0)</param>
        /// <param name="xHStuID">新华学生号</param>
        /// <param name="stuName">姓名</param>
        /// <param name="stuGender">性别</param>
        /// <param name="stuPersonID">身份证号</param>
        /// <param name="stuClassName">班级名</param>
        /// <param name="stuState">在校状况（0：毕业； 1：在校； 2：休学； 3：开学）</param>
        /// <returns>返回结果：MODEL.Student类 异常/null；若无班级/student中StudentClassID 为 -1</returns>
        public MODEL.Student ToStudentModel(int stuID,string xHStuID, string stuName, string stuGender,
            string stuPersonID, int stuClassID, string stuState)
        {
            MODEL.Student stu = new MODEL.Student();

            StringBuilder sb = new StringBuilder();         
          
          
          
            stu.StudentID = stuID;
            stu.XHStudentID = xHStuID;
            stu.StudentName = stuName;
            stu.StudentGender = stuGender;
            stu.StudentPersonID = stuPersonID;
            stu.StudentClassID = stuClassID;
            switch (stuState.Trim())
            {
                case "在校":
                    stu.StudentState = 1;
                    break;
                case "休学":
                    stu.StudentState = 2;
                    break;
                case "毕业":
                    stu.StudentState = 0;
                    break;
                case "开除":
                    stu.StudentState = 3;
                    break;
            }
            return stu;
        }

        /// <summary>
        /// 验证增加或修改学生必要信息是否为空
        /// </summary>
        /// <param name="stu">学生类</param>
        /// <returns>返回结果int 通过验证/-10;</returns>
        public int verify(MODEL.Student stu)
        {
            if (string.IsNullOrEmpty(stu.XHStudentID))
            {
                return -11; 
            }
            if (string.IsNullOrEmpty(stu.StudentName))
            {
                return -12;
            }
            if (string.IsNullOrEmpty(stu.StudentGender))
            {
                return -13;
            }
            if (string.IsNullOrEmpty(stu.StudentClassID.ToString()))
            {
                return -14;
            }
            return -10;
        }

        #endregion

    }
}
